package com.sms.common.util.jdbc;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * @author zengdan E-mail:
 * @version 创建时间：2015年5月26日下午1:10:53
 *          操纵mysql的工具类
 */
public class JdbcBaseDao {

	//使用日志
	private static final Logger logger = LoggerFactory.getLogger(JdbcBaseDao.class);
	private static final JdbcConfig config = new JdbcConfig();
	private static final ComboPooledDataSource dataSource = new ComboPooledDataSource();
	private static JdbcBaseDao jdbcBaseDao;

	public static synchronized JdbcBaseDao getInstance() {
		if (jdbcBaseDao == null) {
			jdbcBaseDao = new JdbcBaseDao();
		}
		return jdbcBaseDao;
	}

	static {
		try {
			Class.forName(config.getDriver());
			try {
				dataSource.setDriverClass(config.getDriver());
			} catch (PropertyVetoException e) {
				logger.error("", e);
			}
			dataSource.setUser(config.getUser());
			dataSource.setPassword(config.getPassword());
			dataSource.setJdbcUrl(config.getURL());
			dataSource.setMaxPoolSize(config.getMaxPoolSize());//最大连接数
			dataSource.setMinPoolSize(config.getMinPoolSize());//最小连接数
			dataSource.setAcquireIncrement(config.getIncrement());//链接耗尽时一次申请的连接数
			dataSource.setInitialPoolSize(config.getInitPoolSize());//初始化时获取3个链接
			dataSource.setMaxIdleTime(config.getMaxIdleTime());//最大空闲时间,超过此时间将关闭空闲连接
			dataSource.setIdleConnectionTestPeriod(config.getIdleConnTestPeriod());//每60秒检查空闲连接
			dataSource.setAutomaticTestTable(config.getAutoTestTable());//c3p0测试表
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			logger.error("加载驱动失败:", e);
		}
	}

	/**
	 * 获取数据库连接
	 */
	public Connection getConnection() {
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	/**
	 * 关闭数据库连接，注意关闭的顺序
	 */
	public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("关闭ResultSet失败，详见：", e);
			}
		}
		if (ps != null) {
			try {
				ps.close();
				ps = null;
			} catch (SQLException e) {
				e.printStackTrace();
				logger.error("关闭PreparedStatement失败，详见：", e);
			}
		}
		if (conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

	/**
	 * 为预编译对象设置参数
	 *
	 * @throws SQLException
	 */
	public void setParameter(PreparedStatement ps, Object... obj) throws SQLException {
		if (obj != null && obj.length > 0) {
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i + 1, obj[i]);
			}
		}
	}

	/**
	 * 增加、修改、删除的方法
	 * obj:可变参数列表
	 */
	public int update(String sql, Object... obj) {
		Connection conn = getConnection();
		PreparedStatement ps = null;
		int rows = 0;
		try {
			ps = conn.prepareStatement(sql);
			setParameter(ps, obj);
			rows = ps.executeUpdate();
		} catch (SQLException e) {
			logger.error("数据库操作异常，详见：", e);
			logger.error("SQL:{},参数列表{}", sql, Arrays.toString(obj));
		} catch (Throwable e) {
			logger.error("未知错误", e);
			logger.error("SQL:{},参数列表{}", sql, Arrays.toString(obj));
		} finally {
			close(null, ps, conn);
			logger.debug("释放资源成功!");
		}
		return rows;
	}

	/**
	 * 查询一个对象的方法
	 * sql ： 要执行的sql语句
	 * obj ： 可变参数列表
	 */
	public Map<String, Object> query(String sql, Object... obj) {
		Map<String, Object> data = null;
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			setParameter(ps, obj);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				data = new HashMap<String, Object>();
				for (int i = 0; i < columnCount; i++) {
					String columnLabel = rsmd.getColumnLabel(i + 1);
					data.put(columnLabel, rs.getObject(columnLabel));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("数据库操作异常，详见", e);
			logger.error("SQL:{},参数列表{}", sql, Arrays.toString(obj));
		} catch (Throwable e) {
			logger.error("未知错误", e);
			logger.error("SQL:{},参数列表{}", sql, Arrays.toString(obj));
		} finally {
			close(rs, ps, conn);
			logger.debug("释放资源成功");
		}
		return data;
	}

	/**
	 * 查询多个对象的方法
	 * sql : 要执行的sql语句
	 * obj : 可变参数列表
	 */
	public List<Map<String, Object>> queryList(String sql, Object... obj) {
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
		Connection conn = getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			setParameter(ps, obj);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Map<String, Object> row = null;
			while (rs.next()) {
				row = new HashMap<String, Object>();
				for (int i = 0; i < columnCount; i++) {
					String columnLabel = rsmd.getColumnLabel(i + 1);
					row.put(columnLabel, rs.getObject(columnLabel));
				}
				data.add(row);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error("数据库操作异常，详见", e);
			logger.error("SQL:{},参数列表{}", sql, Arrays.toString(obj));
		} catch (Throwable e) {
			logger.error("未知错误", e);
			logger.error("SQL:{},参数列表{}", sql, Arrays.toString(obj));
		} finally {
			close(rs, ps, conn);
			logger.debug("释放资源成功!");
		}
		return data;
	}

	/**
	 * 增加、修改、删除的方法
	 * data 同步数据库数据，数据表格数据相同字段数目相同
	 * use BY ZJ
	 */
	public int synchro(String sql, ResultSet rs) {
		if (rs == null) {
			return 0;
		}
		Connection conn = getConnection();
		PreparedStatement ps = null;
		Object obj = null;
		int rows = 0;
		try {
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			//解析rs拿出数据
			while (rs.next()) {
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < columnCount; i++) {
					String columnLabel = rsmd.getColumnLabel(i + 1);
					ps.setObject(i + 1 , rs.getObject(columnLabel));
				}
				ps.executeUpdate();
				rows ++;
			}
		} catch (SQLException e) {
			logger.error("数据库操作异常，详见：", e);
			logger.error("SQL:{},参数列表{}", sql, rs.toString());
		} catch (Throwable e) {
			logger.error("未知错误", e);
			logger.error("SQL:{},参数列表{}", sql, rs.toString());
		} finally {
			close(null, ps, conn);
			logger.debug("释放资源成功!");
		}
		return rows;
	}
	
	public static void main(String[] args) {
		List<Map<String, Object>> map = JdbcBaseDao.getInstance().queryList("select * from t_certificate_info limit 100", null);
		
		System.out.println(map);
	}
}
